﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using DTO;
namespace DAO
{
    public class CTHoaDonDAO
    {
        public List<DTO.CTHoaDonDTO> LayChiTietHoaDon(int MaHD)
        {
            string sql = "select MaHD,MaSP,SoHD,TenSP,SoLuong,DonGia,ThanhTien,DiemCV from CTHoaDon where MaHD=" + MaHD.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            OleDbDataReader rd = cmd.ExecuteReader();
            List<DTO.CTHoaDonDTO> lst=new List<CTHoaDonDTO>();
            while (rd.Read())
            {
                DTO.CTHoaDonDTO dto = new CTHoaDonDTO();
                dto.MaHD = rd.GetInt32(0);
                dto.MaSP = rd.GetInt32(1);
                dto.SoHD = "0";
                dto.TenSP = rd.GetString(3);
                dto.SoLuong = rd.GetInt32(4);
                dto.DonGia = rd.GetDouble(5);
                dto.ThanhTien = rd.GetDouble(6);
                dto.DiemCV = rd.GetInt32(7);
                lst.Add(dto);
            }
            conn.Close();
            return lst;
        }
        public OleDbDataAdapter LayTatCaCTHoaDon(int MaHD)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
          //  conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select MaHD,MaSP,SoHD,TenSP,SoLuong,DonGia,ThanhTien,DiemCV from CTHoaDon where MaHD=" + MaHD.ToString(), conn);
            conn.Close();
            return da;
        }
        public System.Data.DataTable LayCTHoaDon(int MaHD)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbDataAdapter da = new OleDbDataAdapter("select ct.MaHD,ct.MaSP,ct.TenSP,ct.SoLuong,ct.DonGia,ct.ThanhTien,ct.DiemCV,hd.NgayHD,nv.MaNV,nv.HoTen from CTHoaDon ct,HoaDon hd,NhanVien nv where nv.MaNV=hd.MaNV and hd.MaHD=ct.MaHD and ct.MaHD=" + MaHD.ToString(), conn);
            System.Data.DataSet ds = new System.Data.DataSet();
            da.Fill(ds, "CTHDds");
            System.Data.DataTable dt = ds.Tables[0];
            conn.Close();
            return dt;
        }
        public double TongTien(int MaHD)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select SUM(ThanhTien) from CTHoaDon where MaHD="+MaHD.ToString();
            double tt = (double)cmd.ExecuteScalar();
            conn.Close();
            return tt;
        }
        public int TongCV(int MaHD)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select SUM(DiemCV) from CTHoaDon where MaHD=" + MaHD.ToString();
            int tcv = (int)cmd.ExecuteScalar();
            conn.Close();
            return tcv;
        }
        public int XoaCTHoaDon(int MaHD)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "delete from CTHoaDon where MaHD=" + MaHD.ToString();
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
        public int XoaCTHoaDon(int MaHD,int MaSP)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "delete from CTHoaDon where MaHD=" + MaHD.ToString()+" and MaSP="+MaSP.ToString();
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
        public int ThemCTHoaDon(CTHoaDonDTO ct)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "Insert into CTHoaDon(MaHD,MaSP,SoHD,TenSP,SoLuong,DonGia,ThanhTien,DiemCV) values(@MaHD,@MaSP,@SoHD,@TenSP,@SoLuong,@DonGia,@ThanhTien,@DiemCV)";
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            OleDbParameter pa;
            pa = new OleDbParameter("@MaHD", OleDbType.Integer);
            pa.Value = ct.MaHD;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@MaSP", OleDbType.Integer);
            pa.Value = ct.MaSP;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@SoHD", OleDbType.Integer);
            pa.Value = "0";
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@TenSP", OleDbType.VarChar);
            pa.Value = ct.TenSP;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@SoLuong", OleDbType.Integer);
            pa.Value = ct.SoLuong;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@DonGia", OleDbType.Double);
            pa.Value = ct.DonGia;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@ThanhTien", OleDbType.Double);
            pa.Value = ct.ThanhTien;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@DiemCV", OleDbType.Integer);
            pa.Value = ct.DiemCV;
            cmd.Parameters.Add(pa);
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
        public int CapNhatCTHoaDon(CTHoaDonDTO ct)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "Insert into CTHoaDon(MaHD,MaSP,SoHD,TenSP,SoLuong,DonGia,ThanhTien,DiemCV) values(@MaHD,@MaSP,@SoHD,@TenSP,@SoLuong,@DonGia,@ThanhTien,@DiemCV)";
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            OleDbParameter pa;
            pa = new OleDbParameter("@MaHD", OleDbType.Integer);
            pa.Value = ct.MaHD;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@MaSP", OleDbType.Integer);
            pa.Value = ct.MaSP;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@SoHD", OleDbType.Integer);
            pa.Value = "0";
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@TenSP", OleDbType.VarChar);
            pa.Value = ct.TenSP;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@SoLuong", OleDbType.Integer);
            pa.Value = ct.SoLuong;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@DonGia", OleDbType.Double);
            pa.Value = ct.DonGia;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@ThanhTien", OleDbType.Double);
            pa.Value = ct.ThanhTien;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@DiemCV", OleDbType.Integer);
            pa.Value = ct.DiemCV;
            cmd.Parameters.Add(pa);
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
        public CTHoaDonDTO laySPTrongHoaDon(int maHD,int maSP)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd=new OleDbCommand();
            cmd.Connection=conn;
            cmd.CommandText="select * from CTHoaDon where MaHD=@maHD and MaSP=@maSP";
            OleDbParameter pa=new OleDbParameter("@maHD",OleDbType.Integer);
            pa.Value=maHD;
            cmd.Parameters.Add(pa);
            pa=new OleDbParameter("@maSP",OleDbType.Integer);
            pa.Value=maSP;
            cmd.Parameters.Add(pa);
            OleDbDataReader rd=cmd.ExecuteReader();
            rd.Read();
            CTHoaDonDTO cthd=new CTHoaDonDTO();
            cthd.MaHD=rd.GetInt32(0);
            cthd.MaSP=rd.GetInt32(1);
            cthd.SoHD=rd.GetString(2);
            cthd.TenSP=rd.GetString(3);
            cthd.SoLuong=rd.GetInt32(4);
            cthd.DonGia=rd.GetInt32(5);
            cthd.ThanhTien=rd.GetDouble(6);
            cthd.DiemCV=rd.GetInt32(1);
            conn.Close();
            return cthd;
        }
    }
}
